package com.reminis.exceldemo.mapper;

import com.reminis.exceldemo.entity.TextOrderEntry;
import com.reminis.exceldemo.entity.TextShipmentEntry;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface TextYejiMapper {

    // Clear (truncate) the temporary order table
    @Update("TRUNCATE TABLE 下单表") // Replace '下单表' with your actual table name if different
    void truncateOrderTable();

    // Clear (truncate) the temporary shipment table
    @Update("TRUNCATE TABLE 发货表") // Replace '发货表' with your actual table name if different
    void truncateShipmentTable();

    // Batch insert into the order table
    @Insert({"<script>",
             "INSERT INTO 下单表 (`行标签`, `下单数量`, `下单金额`) VALUES",
             "<foreach collection='list' item='item' index='index' separator=','>",
             // Reference the new English field names from TextOrderEntry
             "(#{item.label}, #{item.orderQuantity}, #{item.orderAmount})", 
             "</foreach>",
             "</script>"})
    int batchInsertOrders(@Param("list") List<TextOrderEntry> orderEntries);

    // Batch insert into the shipment table
    // IMPORTANT: Verify the target column names (`发货数量`, `发货金额`?) match TextShipmentEntry fields
    @Insert({"<script>",
             // Assuming target columns are `行标签`, `发货数量`, `发货金额` based on TextShipmentEntry fields
             "INSERT INTO 发货表 (`行标签`, `发货数量`, `发货金额`) VALUES", 
             "<foreach collection='list' item='item' index='index' separator=','>",
             // Reference the new English field names from TextShipmentEntry
             "(#{item.label}, #{item.shipmentQuantity}, #{item.shipmentAmount})", 
             "</foreach>",
             "</script>"})
    int batchInsertShipments(@Param("list") List<TextShipmentEntry> shipmentEntries);

    // Step 1 of Yeji update: Clear relevant fields in 周业绩表
    @Update("UPDATE 周业绩表 SET 下单数量 = NULL, 下单金额 = NULL, 发货数量 = NULL, 发货金额 = NULL")
    void clearYejiStats();

    // Step 2 of Yeji update: Update using data from 下单表
    // This updates existing rows in 周业绩表 based on matching zhonglei in 下单表
    @Update("UPDATE 周业绩表 yj " +
            "JOIN (SELECT `行标签`, SUM(CAST(`下单数量` AS DECIMAL(19,4))) as total_quantity, SUM(CAST(`下单金额` AS DECIMAL(19,4))) as total_amount " +
            "      FROM 下单表 GROUP BY `行标签`) AS orders " +
            "ON yj.中类 = orders.行标签 " +
            "SET yj.下单数量 = orders.total_quantity, yj.下单金额 = orders.total_amount")
    int updateYejiFromOrders();

    // Step 3 of Yeji update: Update using data from 发货表
    // This updates existing rows in 周业绩表 based on matching zhonglei in 发货表
    @Update("UPDATE 周业绩表 yj " +
            "JOIN (SELECT `行标签`, SUM(CAST(`发货数量` AS DECIMAL(19,4))) as total_quantity, SUM(CAST(`发货金额` AS DECIMAL(19,4))) as total_amount " +
            "      FROM 发货表 GROUP BY `行标签`) AS shipments " +
            "ON yj.中类 = shipments.行标签 " +
            "SET yj.发货数量 = shipments.total_quantity, yj.发货金额 = shipments.total_amount")
    int updateYejiFromShipments();

    /**
     * Finds which of the provided labels exist as '中类' in the '周业绩表'.
     * @param labels A list of labels (行标签) to check.
     * @return A list containing only the labels from the input list that exist in '周业绩表'.
     */
    /**
     * 查询提供的标签列表中，哪些标签作为 '中类' 存在于 '周业绩表' 中。
     * @param labels 需要检查的标签 (行标签) 列表。
     * @return 一个列表，仅包含输入列表中存在于 '周业绩表' 的标签。
     */
    @Select({"<script>",
             "SELECT DISTINCT LOWER(中类)", // Convert to lowercase for comparison
             "FROM 周业绩表",
             "WHERE LOWER(中类) IN", // Compare in lowercase
             "<foreach item='item' collection='labels' open='(' separator=',' close=')'>",
             "LOWER(#{item})", // Convert input item to lowercase
             "</foreach>",
             "</script>"})
    List<String> findExistingZhongleiInYeji(@Param("labels") List<String> labels);

} 